package com.yang.dao;

import com.yang.entity.Product;

import java.sql.*;
import java.util.*;

public class ProductDao {
    //根据sql查询   多功能查询
    public List<Product> queryBySql(String sql){
        Connection conn= BaseDao.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List list=new ArrayList();
            while(rs.next()){
                Product p=new Product();
                p.setCategoryid(rs.getInt("Categoryid"));
                p.setDetail(rs.getString("Detail"));
                p.setId(rs.getInt("Id"));
                p.setName(rs.getString("Name"));
                p.setPrice(rs.getDouble("Price"));
                p.setStock(rs.getInt("Stock"));
                p.setUrl(rs.getString("Url"));
                list.add(p);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            BaseDao.close(rs,ps,conn);
        }
        return null;
    }
    //sql修改
    public int updateBySql(String sql) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        try {
            ps=conn.prepareStatement(sql);
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, ps, conn);
        }
        return 0;
    }
    //查询商品前八条数据
    public List<Product> queryProduct() {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement("select * from Product order by id asc limit 1,8 ");
            rs = ps.executeQuery();
            List<Product> list = new ArrayList<>();
            while (rs.next()) {
                Product obj = new Product();
                obj.setId(rs.getInt("id"));
                obj.setName(rs.getString("name"));
                obj.setUrl(rs.getString("url"));
                obj.setPrice(rs.getDouble("price"));
                obj.setStock(rs.getInt("stock"));
                obj.setDetail(rs.getString("detail"));
                obj.setCategoryid(rs.getInt("categoryid"));
                list.add(obj);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, ps, conn);
        }
        return null;
    }

    //单个查询
    public Product queryProductById(Integer id) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement("select * from Product where id=?");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                Product obj = new Product();
                obj.setId(rs.getInt("id"));
                obj.setName(rs.getString("name"));
                obj.setUrl(rs.getString("url"));
                obj.setPrice(rs.getDouble("price"));
                obj.setStock(rs.getInt("stock"));
                obj.setDetail(rs.getString("detail"));
                obj.setCategoryid(rs.getInt("categoryid"));
                return obj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, ps, conn);
        }
        return null;
    }

    //添加
    public int saveProduct(Product obj) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("insert into product(name, url, price, stock, detail, categoryId) values (?, ?, ?, ?, ?, ?)");
            ps.setString(1, obj.getName());
            ps.setString(2, obj.getUrl());
            ps.setObject(3, obj.getPrice());
            ps.setInt(4, obj.getStock());
            ps.setString(5, obj.getDetail());
            ps.setInt(6, obj.getCategoryid());
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, ps, conn);
        }
        return 0;
    }

    //删除
    public int deleteProductById(Integer id) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("delete from Product where id=?");
            ps.setInt(1, id);
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, ps, conn);
        }
        return 0;
    }

    //修改
    public int updateProductById(Product obj) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("update product set name=?, url=?, price=?, stock=?, detail=?, categoryId=? where id=?");
            ps.setString(1, obj.getName());
            ps.setString(2, obj.getUrl());
            ps.setObject(3, obj.getPrice());
            ps.setInt(4, obj.getStock());
            ps.setString(5, obj.getDetail());
            ps.setInt(6, obj.getCategoryid());
            ps.setInt(7, obj.getId());
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, ps, conn);
        }
        return 0;
    }
    //根据id查询
    public Product queryById(int id){
        Connection conn= BaseDao.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement("select * from product where id=?");
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                Product p=new Product();
                p.setCategoryid(rs.getInt("Categoryid"));
                p.setDetail(rs.getString("Detail"));
                p.setId(rs.getInt("Id"));
                p.setName(rs.getString("Name"));
                p.setPrice(rs.getDouble("Price"));
                p.setStock(rs.getInt("Stock"));
                p.setUrl(rs.getString("Url"));
                return  p;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            BaseDao.close(rs,ps,conn);
        }
        return null;
    }
}
